package com.qf.mapper2;

import com.qf.domain2.Classes;
import com.qf.domain2.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

@CacheNamespace //开启二级缓存支持（基于mapper级别-只要是同一个mapper，不管是否是同一个sqlsession，都能通过缓存获取数据）
public interface StudentMapper {

//    @Select("SELECT s.*,c.id curse_id,c.name couse_name\n" +
//            "FROM student s LEFT JOIN stu_cr sc ON s.id=sc.`sid`\n" +
//            "\tLEFT JOIN course c ON  sc.`cid`=c.id")
//    @ResultMap("stuMap")

    @Select("select * from student")
    List<Student> findAll();

    @Select("SELECT s.*,c.id curse_id,c.name couse_name\n" +
            "FROM student s LEFT JOIN stu_cr sc ON s.id=sc.`sid`\n" +
            "\tLEFT JOIN course c ON  sc.`cid`=c.id\n" +
            "WHERE s.id = #{id}")
    @ResultMap("stuMap")
    List<Student> findById(Integer id);


    /**
     * 查询所有学生同时，需要查询选课及班级
     * @return
     */
    @Select("SELECT s.*,c.id curse_id,c.name couse_name,cl.name cname\n" +
            "FROM student s LEFT JOIN stu_cr sc ON s.id=sc.`sid`\n" +
            "\tLEFT JOIN course c ON  sc.`cid`=c.id\n" +
            "\tLEFT JOIN classes cl ON cl.id=s.cid")
    @ResultMap("stuMap_classes")
    List<Student> findAll_classes();


    /**
     * 基于纯注解实现多表数据的联查操作
     */
    @Select("select * from student")
    @Results({
          @Result(column = "id",property = "id"),
          @Result(column = "name",property = "name"),
          @Result(column = "age",property = "age"),
          @Result(column = "cid",property = "cid"),

          //根据当前查询结果中学生的id，去查询学生id对应的课程集合
          @Result(column = "id",property = "courses",
                    javaType = List.class,
                    many =@Many(select = "com.qf.mapper2.CourseMapper.findBySid")
          ),
          @Result(column = "cid",property = "classes",
                    javaType = Classes.class,
                    one = @One(select = "com.qf.mapper2.ClassesMapper.findById")
          )
    })
    List<Student> findAll_anno();
}
